﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace AMS.Inventory.PCClient.InventoryHelpClass
{
    public class SqlDb
    {
        //打开数据库连接 
        string DbConnectionstring = "";//连接字符串
        SqlConnection DbConnection = null;


      

        public bool OpenCon()
        {
            try
            {

                //DbConnectionstring = @"Data Source=192.168.1.11;Initial Catalog=AMS_Inventory;User Id=sa;Password=vitcodesa";
                DbConnectionstring =
                    global::AMS.Inventory.PCClient.Properties.Settings.Default.AMSUpload_ConnectionString;
                DbConnection = new SqlConnection(DbConnectionstring);
                DbConnection.Open();
                return true;



            }
            catch (Exception ex)
            {
                return false;
            }

        }




        //关闭数据连接对象
        public bool CloseConnection()
        {
            if (this.DbConnection != null)
            {
                try
                {
                    DbConnection.Close();
                    DbConnection.Dispose();
                    DbConnection = null;

                    return true;
                }
                catch
                {
                    return false;
                }
            }
            else
            {
                return true;
            }

        }


        //向 SDF 执行 SQL 句
        public int ExecNoneQuery(string sSql)
        {
            OpenCon();
            int result = -1;
            try
            {
                SqlCommand CeSqlCmd = DbConnection.CreateCommand();
                CeSqlCmd.CommandText = sSql;
                result = CeSqlCmd.ExecuteNonQuery();

                return result;
            }
            catch (Exception ex)
            {

                return result;
            }
            finally
            {
                CloseConnection();
            }

        }
        #region 取指定表的编号
        public string getTabNo(string sql)
        {
            string tabNo = "";
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            ds = this.GetDataSet(sql);
            if (ds != null)
                dt = this.GetDataSet(sql).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                tabNo = dt.Rows[0][0].ToString();
            }
            return tabNo;
        }
        # endregion

        //获取数据集 Table
        public DataSet GetDataSet(string sCeSql)
        {
            try
            {
                OpenCon();
                DataSet dtResult = new DataSet();

                SqlCommand CeSqlCmd = DbConnection.CreateCommand();

                SqlDataAdapter CeSqlDataAda = new SqlDataAdapter(CeSqlCmd);

                CeSqlCmd.CommandText = sCeSql;

                CeSqlDataAda.Fill(dtResult);


                return dtResult;

            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }

        public int GetCount(string sql)
        {
            try
            {
                OpenCon();
                var dtResult = new DataSet();

                SqlCommand CeSqlCmd = DbConnection.CreateCommand();

                SqlDataAdapter CeSqlDataAda = new SqlDataAdapter(CeSqlCmd);

                CeSqlCmd.CommandText = sql;

                CeSqlDataAda.Fill(dtResult);

                if(dtResult.Tables[0].Rows.Count > 0)
                {
                    return Convert.ToInt32(dtResult.Tables[0].Rows[0][0].ToString());
                }
                return 0;

            }
            catch (Exception ex)
            {
                return 0;
            }
            finally
            {
                CloseConnection();
            }
        }


        // 根据编号删除数据
        public int deleteInfoById(int id, string tabName)
        {

            string Sql = " delete  from  " + tabName + " where id='" + id + "'";
            int Access = 0;
            Access = this.ExecNoneQuery(Sql);
            return Access;


        }
        /// <summary>
        /// 将dataset转换为datatable
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public DataTable getDtFromDs(DataSet ds)
        {
            DataTable dt = new DataTable();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                dt = ds.Tables[0];
            }
            return dt;
        }

        ///</summary>
        /// <param name="procName">命令文本</param>
        /// <param name="prams"命令文本所需参数</param>
        /// <returns>返回SqlCommand对象</returns>
        private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
        {
            // 确认打开连接
            OpenCon();
            SqlCommand cmd = new SqlCommand(procName, DbConnection);
            cmd.CommandType = CommandType.Text;　　　　 //执行类型：命令文本

            // 依次把参数传入命令文本
            cmd.Parameters.Clear();
            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    if (!cmd.Parameters.Contains(parameter.ParameterName))
                        cmd.Parameters.Add(parameter);
                }
            }
            // 加入返回参数
            cmd.Parameters.Add(
                new SqlParameter("ReturnValue", SqlDbType.Int, 4,
                                 ParameterDirection.ReturnValue, false, 0, 0,
                                 string.Empty, DataRowVersion.Default, null));

            return cmd;
        }
        ///<summary>
        ///使用存储过程        
        ///</summary>
        ///<param name ="procedureName">命令文本</param>
        ///<param name ="Params">参数对象</param>
        ///<returns></returns>
        public DataTable GetTableRunProc(string procedureName, SqlParameter[] prams)
        {
            try
            {
                SqlCommand cmd = CreateCommand(procedureName, prams);
                cmd.CommandType = CommandType.StoredProcedure;
                DataSet ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
                CloseConnection();
                if (ds != null)
                {
                    if (ds.Tables != null)
                    {
                        return ds.Tables[0];
                    }
                    else
                    {
                        return null;

                    }
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
// ReSharper disable PossibleIntendedRethrow
                throw ex;
// ReSharper restore PossibleIntendedRethrow
            }
        



        }

    }
}